package com.migrator.ui;

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JList;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;

import com.migrator.ctes.Driver;
import com.migrator.db.MySQL;
import com.migrator.db.PostgreSQL;
import com.migrator.db.SQLite;
import com.migrator.utils.Database;
import com.migrator.utils.Migrator;
import com.migrator.utils.ResourceLoader;
import java.awt.Dimension;
import java.io.File;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.AbstractCollection;
import java.util.ArrayList;
import java.util.Collection;
import javax.swing.JFileChooser;
import javax.swing.JTable;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.TableModel;



public class MigratorUI extends JFrame {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -8708562991917690995L;
	private ResourceLoader loader = new ResourceLoader("/toolbarButtonGraphics/general/");
	private JMenuBar menu = new JMenuBar() ;
	private JMenu mySQLMenu = new JMenu("MySQL");
        private JMenu postgreSQLMenu = new JMenu("PostgreSQL");
        private JMenu SQLiteMenu = new JMenu("SQLite");
	private JMenuItem myToPs = new JMenuItem("to PostGres") ;
        private JMenuItem myToLite = new JMenuItem("to SQLite") ;
	private JMenuItem psToMy = new JMenuItem("to Mysql") ;
        private JMenuItem psToLite = new JMenuItem("to SQLite") ;
        private JMenuItem liteToMy = new JMenuItem("to MySQL") ;
        private JMenuItem liteToPs = new JMenuItem("to PostgreSQL") ;
	private JButton connectMysql =new JButton("Charger Mysql",loader.getIcon("Open24.gif")) ;
	private JButton connectPostgres =new JButton("Charger Postgres" ,loader.getIcon("Open24.gif")) ;
	private JButton connectSqlite =new JButton("Charger Sqlite",loader.getIcon("Open24.gif")) ;
	private JList listMysql = new JList();
	private JList listPostgres  = new JList();
	private JList listSqlite  = new JList();
	private JList listTable = new JList();
        private JTable contain = new JTable() ;
	private JPanel screen = new JPanel(new BorderLayout()) ;
	private Database mysql = new MySQL() ;
	private Database psql = new PostgreSQL() ;
        private Database mysqlTmp = new MySQL() ;
        private Database psqlTmp = new PostgreSQL() ;
        private SQLite sqliteTmp = new SQLite();
        private SQLite sqlite = new SQLite() ;
	private String urlConnectionMysql;
	private String urlConnectionPs;
        private String urlConnectionSqlite;
        private String bddSelected ;
        private String path;
	private Properties propMy = new Properties();
	private Properties propPs = new Properties();
        private JFileChooser fc = new JFileChooser();
        File file = null;
	
	
	
	public MigratorUI(){
		
		this.setSize(800,600);
		this.setTitle("Database Migrator v1 ");
		this.setLocationRelativeTo(null);
		this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		this.addWindowListener(new WindowAdapter() {
			public void windowClosed(WindowEvent e){
				mysql.close() ;
				psql.close();
				
			}
		})		;
		initComponents() ;
		
		this.setJMenuBar(menu) ;
		this.setContentPane(screen);
		
		this.setVisible(true);
		
		
	}
	
	private void initComponents(){
	
		mySQLMenu.add(myToPs);
                mySQLMenu.add(myToLite);

		postgreSQLMenu.add(psToMy) ;
                postgreSQLMenu.add(psToLite) ;
                
                SQLiteMenu.add(liteToMy);
                SQLiteMenu.add(liteToPs);

		menu.add(mySQLMenu) ;
                menu.add(postgreSQLMenu) ;
                menu.add(SQLiteMenu);
	
		JPanel connector = new JPanel(new FlowLayout(FlowLayout.CENTER, 15, 15)) ;
		connector.add(connectMysql) ;
		connector.add(connectPostgres) ;
		connector.add(connectSqlite) ;
		
		JPanel listBdd = new JPanel(new BorderLayout(5,5));
		listBdd.add(new JScrollPane(listMysql) , BorderLayout.WEST) ;
		listBdd.add(new JScrollPane(listPostgres) , BorderLayout.CENTER) ;
		listBdd.add(new JScrollPane(listSqlite) , BorderLayout.EAST) ;

                JPanel contenuTable = new JPanel(new BorderLayout(5,5)) ;
                contenuTable.setPreferredSize(new Dimension(100, 150)) ;
                contenuTable.add(new JScrollPane(listTable),BorderLayout.CENTER);
                contenuTable.add(new JScrollPane(contain),BorderLayout.EAST);

		
		screen.add(connector,BorderLayout.NORTH);
		screen.add(listBdd,BorderLayout.CENTER) ;
		//screen.add(new JScrollPane(listTable), BorderLayout.SOUTH);
		screen.add(contenuTable, BorderLayout.SOUTH);

		myToPs.addActionListener(new MigratorListener()) ;
                myToLite.addActionListener(new MigratorListener()) ;
		psToMy.addActionListener(new MigratorListener()) ;
                psToLite.addActionListener(new MigratorListener()) ;
		
		connectMysql.addActionListener(new ConnectorListener()) ;
		connectPostgres.addActionListener(new ConnectorListener()) ;
                connectSqlite.addActionListener(new DirectoryChooserListener());
		
		listMysql.addListSelectionListener(new BDDSelectedListener()) ;
		listPostgres.addListSelectionListener(new BDDSelectedListener()) ;
                listSqlite.addListSelectionListener(new BDDSelectedListener()) ;

                listTable.addListSelectionListener(new chowTableListener());
			
	}
	
	public class ConnectorListener implements ActionListener{

		@Override
		public void actionPerformed(ActionEvent e) {
			
			ConnectionPanel con = new ConnectionPanel() ;
			
			int reponse =  JOptionPane.showConfirmDialog(null, con, "Connection", JOptionPane.OK_CANCEL_OPTION, JOptionPane.PLAIN_MESSAGE);
			if(reponse == JOptionPane.OK_OPTION){

				
				if(e.getSource() == connectMysql ){
					try{
						
						propMy.setProperty("user", con.getUsername()) ;
						propMy.setProperty("password", con.getPassword()) ;
						
						urlConnectionMysql = "jdbc:mysql://"+con.getHost() +":"+ con.getPort()+ "/" ;
						mysql.connect(Driver.MYSQL_DRIVER, urlConnectionMysql,propMy) ;
						
						listMysql.setListData(mysql.listCatalogs().toArray(new String[mysql.listCatalogs().size()]) );
					}catch(Exception ex){
						JOptionPane.showMessageDialog(null, "Connexion Failed Please REtry") ;
					}
					
				}
				else if(e.getSource() == connectPostgres){
					try {
						
						propPs.setProperty("user", con.getUsername()) ;
						propPs.setProperty("password", con.getPassword()) ;

                                                
						urlConnectionPs = "jdbc:postgresql://"+con.getHost() +":"+ con.getPort()+ "/";
						System.out.println(urlConnectionPs+"postgres");
						psql.connect(Driver.POSTGRESQL_DRIVER, urlConnectionPs+"postgres" , propPs) ;
						
						listPostgres.setListData(psql.listCatalogs().toArray(new String[psql.listCatalogs().size()]) );
					} catch (Exception e1) {
						// TODO Auto-generated catch block
						JOptionPane.showMessageDialog(null, "Connexion Failed Please Retry") ;
						e1.printStackTrace() ;

					}
					
					
				}
				
			}
			
		}
		
	}
	
	public class BDDSelectedListener implements ListSelectionListener{

		@Override
		public void valueChanged(ListSelectionEvent e) {
			// TODO Auto-generated method stub
			if(!e.getValueIsAdjusting()){
				JList list = (JList) e.getSource() ;
				
				bddSelected = (String) list.getSelectedValue() ;
				
				if(e.getSource() == listMysql){
					try {
						if(mysql.isConnected())
                                                    mysql.close() ;
						mysql.connect(Driver.MYSQL_DRIVER, urlConnectionMysql+bddSelected ,propMy) ;
						
					} catch (Exception e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
					listTable.setListData(mysql.listTables().toArray(new String[mysql.listTables().size()]) );
				} else if (e.getSource() == listPostgres){
					try {
						if(psql.isConnected())
                                                    psql.close() ;
						psql.connect(Driver.POSTGRESQL_DRIVER, urlConnectionPs+bddSelected ,propPs) ;
					} catch (Exception e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
					listTable.setListData(psql.listTables().toArray(new String[psql.listTables().size()]) );
					
				} else if(e.getSource() == listSqlite){
                                    try {
                                        urlConnectionSqlite = "jdbc:sqlite:" + path +"/"+  bddSelected;
                                        if(sqlite.isConnected())
                                           sqlite.close();
                                        sqlite.connect(Driver.SQLITE_DRIVER, urlConnectionSqlite);
                                        System.out.println(urlConnectionSqlite);
                                        listTable.setListData(sqlite.listTables().toArray(new String[sqlite.listTables().size()])) ;
                                    } catch (Exception ex) {
                                        Logger.getLogger(MigratorUI.class.getName()).log(Level.SEVERE, null, ex);
                                    }
                                }
			}
			
		}
		
	}

        public class chowTableListener implements ListSelectionListener{

        @Override
        public void valueChanged(ListSelectionEvent e) {
            if(!e.getValueIsAdjusting()){
                try {
                    boolean lite = false ;
                    String tableName = (String) listTable.getSelectedValue();
                    ResultSet result = null;
                    if ((psql.isConnected()) &&( psql.listTables().contains(tableName) )) {
                        result = psql.executeQuery(" SELECT * FROM " + tableName);
                    } else if ( (mysql.isConnected()) && (mysql.listTables().contains(tableName))) {
                        result = mysql.executeQuery("SELECT * FROM " + tableName);
                    }else if ( (sqlite.isConnected()) && (sqlite.listTables().contains(tableName))) {
                        result = sqlite.executeQuery("SELECT * FROM " + tableName);
                        lite = true;
                    }


                    if(result != null){

                        ResultSetMetaData resultMeta = result.getMetaData();

                        int size = 0 ;
                        if(result != null){
                            result.beforeFirst();
                            result.last();
                            size = result.getRow();
                            result.beforeFirst();
                        }

                        String[] title = new String[resultMeta.getColumnCount()] ;
                        Object[][] data = new Object[size][resultMeta.getColumnCount()] ;

                        for (int i = 1; i <= resultMeta.getColumnCount(); i++) {
                              title[i-1] = resultMeta.getColumnName(i).toUpperCase() ;
                        }
                         int i = 0 ;
                        while(result.next()){
                            i++ ;
                            for (int k = 1; k <= resultMeta.getColumnCount(); k++) {
                                data[i-1][k-1] = result.getObject(k).toString() ;
                            }
                        }
                         myModel model = new myModel(data, title) ;
                         contain.setModel(model);

                         result.close();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(MigratorUI.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        }

        }
	
	public class MigratorListener implements ActionListener{

		@Override
		public void actionPerformed(ActionEvent e) {
			// TODO Auto-generated method stub
			if(e.getSource() == myToPs){
				if(listMysql.isSelectionEmpty()){
					JOptionPane.showMessageDialog(null, "Nothing selected ! please make a choice before migrating") ;
				}
				else{
                                    String bdd = (String) listMysql.getSelectedValue() ;
					try{
						Migrator mig = new Migrator() ;
						mig.setSource(mysql) ;
                                                mig.setDest(psql) ;
                                                mig.dropDatabase(bdd) ;
                                                mig.createDatabase(bdd);
                                                psqlTmp.connect(Driver.POSTGRESQL_DRIVER, urlConnectionPs+bdd, propPs) ;
						mig.setDest(psqlTmp) ;
                                                if(listTable.isSelectionEmpty())
                                                    mig.migrateDatabase(mysql.listTables());
                                                else{
                                                    
                                                    Collection<String> cs = new ArrayList<String>() ;
                                                    for (Object str : listTable.getSelectedValues()) {
                                                        cs.add(str.toString());
                                                    }

                                                    mig.migrateDatabase(cs);
                                                    
                                                }

                                                listPostgres.setListData(psqlTmp.listCatalogs().toArray(new String[psqlTmp.listCatalogs().size()]) );
						listTable.setListData(psql.listTables().toArray(new String[psql.listTables().size()]) );

                                                JOptionPane.showMessageDialog(null, "Migration succeed !!!");
                                                psqlTmp.close();

					}
                                        catch(Exception ex){
						JOptionPane.showMessageDialog(null, "Migration aborted ! something went wrong: "+ex.getMessage());
                                            
					}
				}
			}
			else if(e.getSource() == psToMy){
				if(listPostgres.isSelectionEmpty()){
					JOptionPane.showMessageDialog(null, "Nothing selected ! please make a choice before migrating") ;
				}
				else{
                                    String bdd = (String) listPostgres.getSelectedValue() ;
					try{
						Migrator mig = new Migrator() ;
						mig.setDest(mysql) ;
						mig.setSource(psql) ;
                                                mig.dropDatabase(bdd) ;
                                                mig.createDatabase(bdd);
                                                mysqlTmp.connect(Driver.MYSQL_DRIVER, urlConnectionMysql+bdd, propMy) ;
						mig.setDest(mysqlTmp) ;
						if(listTable.isSelectionEmpty())
                                                    mig.migrateDatabase(psql.listTables());
                                                else{

                                                    Collection<String> cs = new ArrayList<String>() ;
                                                    for (Object str : listTable.getSelectedValues()) {
                                                        cs.add(str.toString());
                                                    }

                                                    mig.migrateDatabase(cs);

                                                }
                                                listMysql.setListData(mysqlTmp.listCatalogs().toArray(new String[mysqlTmp.listCatalogs().size()]) );
						listTable.setListData(mysql.listTables().toArray(new String[mysql.listTables().size()]) );
                                                JOptionPane.showMessageDialog(null, "Migration succeed !!!");
                                                mysqlTmp.close();
					}catch(Exception ex){
						JOptionPane.showMessageDialog(null, "Migration aborted ! something went wrong: "+ex.getMessage());
					}
				}
			}
                        else if(e.getSource()== myToLite){
                            if(listMysql.isSelectionEmpty()){
                                JOptionPane.showMessageDialog(null,  "Nothing selected ! please make a choice before migrating");
                            }else{
                                try {
                                    String bdd = (String) listMysql.getSelectedValue();
                                    Migrator mig = new Migrator();
                                    mig.setSource(mysql);
                                    mig.setDest(sqlite);
                                    sqliteTmp.connect(Driver.SQLITE_DRIVER, "jdbc:sqlite:"+path+"/"+bdd+".db");
                                    mig.setDest(sqliteTmp);
                                    if (listTable.isSelectionEmpty()) {
                                        mig.migrateDatabase(mysql.listTables());
                                    } else {
                                        Collection<String> cs = new ArrayList<String>();
                                        for (Object str : listTable.getSelectedValues()) {
                                            cs.add(str.toString());
                                        }
                                        mig.migrateDatabase(cs);
                                    }
                                    listSqlite.setListData(file.list());
                                    listTable.setListData(sqlite.listTables().toArray(new String[sqlite.listTables().size()]));
                                    JOptionPane.showMessageDialog(null, "Migration succeed !!!");
                                    sqliteTmp.close();
                                } catch (Exception ex) {
                                    Logger.getLogger(MigratorUI.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }

                        }
                        else if(e.getSource()== psToLite){
                            if(listPostgres.isSelectionEmpty()){
                                JOptionPane.showMessageDialog(null,  "Nothing selected ! please make a choice before migrating");
                            }else{
                                try {
                                    String bdd = (String) listPostgres.getSelectedValue();
                                    Migrator mig = new Migrator();
                                    mig.setSource(psql);
                                    mig.setDest(sqlite);
                                    sqliteTmp.connect(Driver.SQLITE_DRIVER, "jdbc:sqlite:"+path+"/"+bdd+".db");
                                    mig.setDest(sqliteTmp);
                                    if (listTable.isSelectionEmpty()) {
                                        mig.migrateDatabase(psql.listTables());
                                    } else {
                                        Collection<String> cs = new ArrayList<String>();
                                        for (Object str : listTable.getSelectedValues()) {
                                            cs.add(str.toString());
                                        }
                                        mig.migrateDatabase(cs);
                                    }
                                    listSqlite.setListData(file.list());
                                    listTable.setListData(sqlite.listTables().toArray(new String[sqlite.listTables().size()]));
                                    JOptionPane.showMessageDialog(null, "Migration succeed !!!");
                                    sqliteTmp.close();
                                } catch (Exception ex) {
                                    Logger.getLogger(MigratorUI.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                        else if(e.getSource()== liteToMy){

                        }
                        else if(e.getSource()== liteToPs){

                        }
		}
		
	}

        class myModel extends AbstractTableModel{
            private Object[][] data ;
            private String [] title ;

            public myModel(Object[][] data, String[] title) {
                this.data = data;
                this.title = title;
            }

            @Override
            public int getRowCount() {
                return this.data.length;
            }

            @Override
            public int getColumnCount() {
                return this.title.length ;
            }

            @Override
            public Object getValueAt(int rowIndex, int columnIndex) {
                return this.data[rowIndex][columnIndex] ;
            }

            @Override
            public String getColumnName(int col) {
		  return this.title[col];
            }

            @Override
            public boolean isCellEditable(int row, int col){
		return false;
            }

        }

        public class DirectoryChooserListener implements ActionListener{

        @Override
        public void actionPerformed(ActionEvent e) {
            fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
           int returnVal = fc.showOpenDialog(MigratorUI.this) ;
           if(returnVal == JFileChooser.APPROVE_OPTION){
               file = fc.getSelectedFile() ;
               path = file.getAbsolutePath();
               listSqlite.setListData(file.list());
           }
        }

        }
	
	public static void main(String args[]){
		
		@SuppressWarnings("unused")
		MigratorUI mi = new MigratorUI() ;
	
		
	}
}
